package com.hymjweb.demo.hymj.finance_manage.statistics.mapper;


import com.hymjweb.demo.framework.sys.SysConstant;
import com.hymjweb.demo.framework.util.DataTypeUtil;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

@Mapper
@Component
public interface CwStatisticsMapper {

    class CwStatisticsMapperProvider{

        public String querySalesmanStatisticsList(Map param){
            String sql= new SQL() {{
                SELECT("t.delivery_customer_saleman_id deliveryCustomerSalemanId, date_format(t.open_bill_date,'%Y-%m') openBillMonth, count(t.id) totalBill, sum(t.total_number) totalNumber, sum(t.actual_weight) totalActualWeight, sum(t.chargeable_weight) totalChargeableWeight, sum(t.volume) totalVolume, sum(t.transport_cost) totalTransportCost, sum(t.insurance_fee) totalInsuranceFee, sum(t.open_bill_delivery_charge) totalOpenBillDeliveryCharge, sum(t.agent_delivery_charge) totalAgentDeliveryCharge, sum(t.tax) totalTax, sum(t.fuel_surcharge) totalFuelSurcharge, sum(t.collection_money) totalCollectionMoney, sum(t.other_charges) totalOtherCharges, sum(t.service_charge) totalServiceCharge, sum(t.actual_transport_cost) totalActualTransportCost, sum(t.delivery_customer_payment_amount) totalDeliveryCustomerPaymentAmount, sum(t.receive_customer_payment_amount) totalReceiveCustomerPaymentAmount, sum(t.total_amount) totalAmount, sum(t.actual_amount) totalActualAmount, sum(t.share_cost) totalShareCost, sum(t.operating_cost) totalOperatingCost, sum(t.gross_profit) totalGrossProfit, sum(t.settlement_amount) totalSettlementAmount");
                FROM("b_native_bill t ");
                WHERE("1=1");
            }}.toString();

            if(param!=null){
                String keyDeliveryCustomerSalemanId= DataTypeUtil.toStringObject(param.get("keyDeliveryCustomerSalemanId"));

                String keyOpenBillStartDate = DataTypeUtil.toNotNullString(param.get("keyOpenBillStartDate"));
                String keyOpenBillEndDate = DataTypeUtil.toNotNullString(param.get("keyOpenBillEndDate"));

                // keySettlementStatusIdJsonDate
                Long keySettlementStatusId = DataTypeUtil.toLong(param.get("keySettlementStatusId"),-1);

                String czyId = DataTypeUtil.toStringObject(param.get("czyId"));
                String departmentId = DataTypeUtil.toStringObject(param.get("departmentId"));

                if(keySettlementStatusId!=-1){
                    sql += " and t.settlement_status_id = " + keySettlementStatusId;
                }

                if(keyDeliveryCustomerSalemanId!=null){
                    sql += " and t.delivery_customer_saleman_id = " + DataTypeUtil.quoteStr(keyDeliveryCustomerSalemanId);
                }

                if(!"".equals(keyOpenBillStartDate)){
                    if(!"".equals(keyOpenBillEndDate)){
                        int d=keyOpenBillStartDate.compareTo(keyOpenBillEndDate);
                        if(d>0){
                            String tempDate = keyOpenBillStartDate;
                            keyOpenBillStartDate = keyOpenBillEndDate;
                            keyOpenBillEndDate = tempDate;
                        }
                        sql += " and (date_format(t.create_time,'%Y-%m-%d') between "+ DataTypeUtil.quoteStr(keyOpenBillStartDate) +" and " + DataTypeUtil.quoteStr(keyOpenBillEndDate) + ")";
                    }
                    else{
                        sql += " and date_format(t.create_time,'%Y-%m-%d') >= "+ DataTypeUtil.quoteStr(keyOpenBillStartDate);
                    }
                }
                else{
                    if(!"".equals(keyOpenBillEndDate)){
                        sql += " and date_format(t.create_time,'%Y-%m-%d') <= "+ DataTypeUtil.quoteStr(keyOpenBillEndDate);
                    }
                }

            }

            sql += " group by t.delivery_customer_saleman_id ,date_format(t.open_bill_date,'%Y-%m')";

            return sql;
        }


        public String queryReceivablesStatisticsList(Map param){
            String sql= new SQL() {{
                SELECT("t.attribution_department_id attributionDepartmentId, t.delivery_customer_id deliveryCustomerId,t.delivery_customer_name deliveryCustomerName,date_format(t.open_bill_date,'%Y-%m') openBillMonth,count(t.id) totalBill, sum(t.total_number) totalNumber, sum(t.settlement_amount) totalSettlementAmount, sum(t.drop_amount) totalDropAmount,sum(t.actual_weight) totalActualWeight, sum(t.chargeable_weight) totalChargeableWeight, sum(t.volume) totalVolume, sum(t.transport_cost) totalTransportCost, sum(t.insurance_fee) totalInsuranceFee, sum(t.open_bill_delivery_charge) totalOpenBillDeliveryCharge, sum(t.agent_delivery_charge) totalAgentDeliveryCharge, sum(t.tax) totalTax, sum(t.fuel_surcharge) totalFuelSurcharge, sum(t.collection_money) totalCollectionMoney, sum(t.other_charges) totalOtherCharges, sum(t.service_charge) totalServiceCharge, sum(t.actual_transport_cost) totalActualTransportCost, sum(t.delivery_customer_payment_amount) totalDeliveryCustomerPaymentAmount, sum(t.receive_customer_payment_amount) totalReceiveCustomerPaymentAmount, sum(t.total_amount) totalAmount, sum(t.actual_amount) totalActualAmount, sum(t.share_cost) totalShareCost, sum(t.operating_cost) totalOperatingCost, sum(t.gross_profit) totalGrossProfit ");
                FROM("b_native_bill t ");
                WHERE("1=1 and t.settlement_status_id = " + SysConstant.XT_DIC_SETTLEMENT_STATUS.UNCLEARED.getId());
            }}.toString();

            if(param!=null){
                String keyDeliveryCustomerSalemanId= DataTypeUtil.toStringObject(param.get("keyDeliveryCustomerSalemanId"));
                String keyAttributionDepartmentId= DataTypeUtil.toStringObject(param.get("keyAttributionDepartmentId"));
                String keyDeliveryCustomerId= DataTypeUtil.toStringObject(param.get("keyDeliveryCustomerId"));

                String keyOpenBillStartDate = DataTypeUtil.toNotNullString(param.get("keyOpenBillStartDate"));
                String keyOpenBillEndDate = DataTypeUtil.toNotNullString(param.get("keyOpenBillEndDate"));
                String keyCk1 = DataTypeUtil.toStringObject(param.get("keyCk1")); // "true","false"

                String czyId = DataTypeUtil.toStringObject(param.get("czyId"));
                String departmentId = DataTypeUtil.toStringObject(param.get("departmentId"));

                if(keyDeliveryCustomerSalemanId!=null){
                    sql += " and t.delivery_customer_saleman_id = " + DataTypeUtil.quoteStr(keyDeliveryCustomerSalemanId);
                }

                if(keyAttributionDepartmentId!=null){

                    if(keyCk1!=null && "true".equals(keyCk1)){
                        // select id from xt_department where FIND_IN_SET(id,getChildList('0000'));
                        sql += " and t.attribution_department_id in (select id from xt_department where FIND_IN_SET(id,getChildList(" + DataTypeUtil.quoteStr(keyAttributionDepartmentId) + ")))";
                    }
                    else{
                        sql += " and t.attribution_department_id = " + DataTypeUtil.quoteStr(keyAttributionDepartmentId);
                    }
                }

                if(keyDeliveryCustomerId!=null){
                    sql += " and t.delivery_customer_id = " + DataTypeUtil.quoteStr(keyDeliveryCustomerId);
                }

                if(!"".equals(keyOpenBillStartDate)){
                    if(!"".equals(keyOpenBillEndDate)){
                        int d=keyOpenBillStartDate.compareTo(keyOpenBillEndDate);
                        if(d>0){
                            String tempDate = keyOpenBillStartDate;
                            keyOpenBillStartDate = keyOpenBillEndDate;
                            keyOpenBillEndDate = tempDate;
                        }
                        sql += " and (date_format(t.create_time,'%Y-%m-%d') between "+ DataTypeUtil.quoteStr(keyOpenBillStartDate) +" and " + DataTypeUtil.quoteStr(keyOpenBillEndDate) + ")";
                    }
                    else{
                        sql += " and date_format(t.create_time,'%Y-%m-%d') >= "+ DataTypeUtil.quoteStr(keyOpenBillStartDate);
                    }
                }
                else{
                    if(!"".equals(keyOpenBillEndDate)){
                        sql += " and date_format(t.create_time,'%Y-%m-%d') <= "+ DataTypeUtil.quoteStr(keyOpenBillEndDate);
                    }
                }

            }

            sql += " group by t.attribution_department_id, t.delivery_customer_id, t.delivery_customer_name, date_format(t.open_bill_date,'%Y-%m')";

            return sql;
        }
    }

    @ResultType(Map.class)
    @SelectProvider(type = CwStatisticsMapperProvider.class, method = "querySalesmanStatisticsList")
    List<Map<String,Object>> querySalesmanStatisticsList(Map param);

    @ResultType(Map.class)
    @SelectProvider(type = CwStatisticsMapperProvider.class, method = "queryReceivablesStatisticsList")
    List<Map<String,Object>> queryReceivablesStatisticsList(Map param);
}
